package org.byqh.dqeai.controller.custom;

import org.apache.commons.lang3.StringUtils;
import org.byqh.dqeai.common.Json;
import org.byqh.dqeai.controller.SysAppDatabaseController;
import org.byqh.dqeai.dto.common.PageModel;
import org.byqh.dqeai.dto.common.ReqParamData;
import org.byqh.dqeai.dto.common.SqlParam;
import org.byqh.dqeai.entity.SysAppDatabase;
import org.byqh.dqeai.util.Base64Util;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.server.ResponseStatusException;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @author ShouWang
 */
@RestController
@RequestMapping("/CodeBuilder")
@Validated
public class DataController {
    static final String mysql = "mysql";
    static final String postgresql = "postgresql";
    static final String oracle = "oracle";
    static final String sqlserver = "sqlserver";

    @Autowired
    private SysAppDatabaseController dbService;

    /**
     * 获取jdbc连接driver
     */
    private String getDriver(ReqParamData req) {
        if (StringUtils.equalsIgnoreCase(mysql, req.getDataSource().getDbtype())) {
            return "com.mysql.jdbc.Driver";
        } else if (StringUtils.equalsIgnoreCase(postgresql, req.getDataSource().getDbtype())) {
            return "org.postgresql.Driver";
        } else if (StringUtils.equalsIgnoreCase(sqlserver, req.getDataSource().getDbtype())) {
            return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        }

        return "oracle.jdbc.driver.OracleDriver";
    }

    /**
     * 获取jdbc连接url
     */
    private String getUrl(ReqParamData req) {
        if (StringUtils.equalsIgnoreCase(mysql, req.getDataSource().getDbtype())) {
            return String.format("jdbc:mysql://%s:%s/%s",
                    req.getDataSource().getIp(),
                    req.getDataSource().getPort(),
                    req.getDataSource().getInstance());
        } else if (StringUtils.equalsIgnoreCase(postgresql, req.getDataSource().getDbtype())) {
            return String.format("jdbc:postgresql://%s:%s/%s",
                    req.getDataSource().getIp(),
                    req.getDataSource().getPort(),
                    req.getDataSource().getInstance());
        } else if (StringUtils.equalsIgnoreCase(sqlserver, req.getDataSource().getDbtype())) {
            return String.format("jdbc:sqlserver://%s:%s;databaseName=%s",
                    req.getDataSource().getIp(),
                    req.getDataSource().getPort(),
                    req.getDataSource().getInstance());
        }

        return String.format("jdbc:oracle:thin:@%s:%s/%s",
                req.getDataSource().getIp(),
                req.getDataSource().getPort(),
                req.getDataSource().getInstance());
    }

    /**
     * 获取分页sql
     */
    private String getPagingSql(ReqParamData req) {
        String sql = req.getSqlStatement().getSql();

        Integer page = req.getSqlStatement().getPage();
        Integer number = req.getSqlStatement().getPageCount();

        if (page != null && number != null) {
            if (StringUtils.equalsIgnoreCase(sqlserver, req.getDataSource().getDbtype())) {
                return String.format(
                        "SELECT * FROM (%s) sq" +
                                "OFFSET %s ROWS " +
                                "FETCH NEXT %s ROWS ONLY; ", sql, (page + 1) * number, page * number);
            } else if (StringUtils.equalsIgnoreCase(oracle, req.getDataSource().getDbtype())) {
                return String.format(
                        "SELECT * " +
                                "FROM (SELECT ROW_.*, ROWNUM ROWNUM_ " +
                                "FROM (%s) ROW_ " +
                                "WHERE ROWNUM <= %s) " +
                                "WHERE ROWNUM_ > %s ", sql, (page + 1) * number, page * number);
            } else {
                return String.format(
                        "SELECT * FROM (%s) sq OFFSET %s LIMIT %s ", sql, (page + 1) * number, page * number);
            }
        }

        return sql;
    }

    /**
     * 获取统计个数sql
     */
    private String getCountSql(ReqParamData req) {
        String sql = req.getSqlStatement().getSql();

        return String.format("SELECT count(*) as counts FROM (%s) sq", sql);
    }

    /**
     * 获取jdbc模板
     */
    private NamedParameterJdbcTemplate getJdbcTemplate(ReqParamData req) {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();

        if (!StringUtils.isBlank(req.getDataSource().getDatabaseId())) {
            SysAppDatabase one = dbService.getById(req.getDataSource().getDatabaseId()).getData();

            if (one != null) {
                req.getDataSource().setDbtype(one.getDatabaseKind());
                req.getDataSource().setIp(one.getDatabaseHost());
                req.getDataSource().setPort(one.getDatabasePort());
                req.getDataSource().setInstance(one.getDatabaseService());
                req.getDataSource().setUsername(one.getDatabaseUsername());
                req.getDataSource().setPassword(one.getDatabasePassword());
            }
        }

        dataSource.setDriverClassName(getDriver(req));
        dataSource.setUrl(getUrl(req));
        dataSource.setUsername(req.getDataSource().getUsername());
        dataSource.setPassword(req.getDataSource().getPassword());

        return new NamedParameterJdbcTemplate(dataSource);
    }

    /**
     * 获取jdbc参数
     */
    private MapSqlParameterSource getParameterSource(ReqParamData req) {
        MapSqlParameterSource paramSource = new MapSqlParameterSource();

        if (req.getSqlStatement().getParamList() != null) {
            for (SqlParam sqlParam : req.getSqlStatement().getParamList()) {
                String key = sqlParam.getKey().replace(":", "");

                if ("datetime".equalsIgnoreCase(sqlParam.getKind())) {
                    paramSource.addValue(key, this.convert2Date(sqlParam.getValue()));
                } else if ("Byte[]".equalsIgnoreCase(sqlParam.getKind())) {
                    paramSource.addValue(key, Base64Util.decode(sqlParam.getValue()));
                } else {
                    paramSource.addValue(key, sqlParam.getValue());
                }
            }
        }

        return paramSource;
    }

    /**
     * 执行rud操作
     */
    public Json executeRUD(ReqParamData req) throws ResponseStatusException {
        int i = 0;
        try {
            NamedParameterJdbcTemplate jdbcTemplate = getJdbcTemplate(req);
            MapSqlParameterSource paramSource = getParameterSource(req);

            i = jdbcTemplate.update(req.getSqlStatement().getSql(), paramSource);
        } catch (DataAccessException e) {
            return Json.exception(e);
        }
        if (i < 1) {
            return Json.error("操作失败！", null, 9999);
        }
        return Json.success(i);
    }

    /**
     * 转换字符串到时间类型
     */
    private Date convert2Date(String dateTime) {
        Date date = null;
        try {
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
            date = df.parse(dateTime);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return date;
    }

    /**
     * 数据查询
     */
    @PostMapping(value = "/select")
    public Json get(@RequestBody ReqParamData reqParamData) throws ResponseStatusException {
        PageModel<Map<String, Object>> page = null;
        try {
            page = new PageModel<>();

            var req = reqParamData;

            NamedParameterJdbcTemplate jdbcTemplate = getJdbcTemplate(req);
            MapSqlParameterSource paramSource = getParameterSource(req);

            String pagingSql = getPagingSql(req);
            String countSql = getCountSql(req);

            SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(pagingSql, paramSource);

            List<Map<String, Object>> res = jdbcTemplate.queryForList(pagingSql, paramSource);

            Map<String, Object> map = jdbcTemplate.queryForMap(countSql, paramSource);

            for (String key : map.keySet()) {
                String counts = map.get(key).toString();
                page.setTotalRecords(Integer.parseInt(counts));
            }

            LinkedHashMap<String, String> mapColumns = new LinkedHashMap<>();
            SqlRowSetMetaData metaData = sqlRowSet.getMetaData();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                mapColumns.put(metaData.getColumnName(i), metaData.getColumnTypeName(i));
            }

            page.setColumns(mapColumns);
            page.setPageNo(req.getSqlStatement().getPage() == null ? 1 : req.getSqlStatement().getPage());
            page.setPageSize(req.getSqlStatement().getPageCount() == null ? page.getTotalRecords() : req.getSqlStatement().getPageCount());
            page.setList(res);
        } catch (Exception e) {
            return Json.exception(e);
        }
        return Json.success(page);
    }

    /**
     * 查询一条数据
     */
    @PostMapping(value = "/selectOne")
    public Json getOne(@RequestBody ReqParamData reqParamData) throws ResponseStatusException {
        Object obj = null;
        try {
            NamedParameterJdbcTemplate jdbcTemplate = getJdbcTemplate(reqParamData);
            MapSqlParameterSource paramSource = getParameterSource(reqParamData);

            obj = jdbcTemplate.queryForObject(getPagingSql(reqParamData), paramSource, Object.class);
        } catch (DataAccessException e) {
            return Json.exception(e);
        }
        return Json.success(obj);
    }

    /**
     * 数据添加
     */
    @PostMapping(value = "/insert")
    public Json post(@RequestBody ReqParamData reqParamData) throws ResponseStatusException {

        return this.executeRUD(reqParamData);
    }

    /**
     * 数据删除
     */
    @PostMapping(value = "/delete")
    public Json delete(@RequestBody ReqParamData reqParamData) throws ResponseStatusException {

        return this.executeRUD(reqParamData);
    }

    /**
     * 数据修改
     */
    @PostMapping(value = "/update")
    public Json put(@RequestBody ReqParamData reqParamData) throws ResponseStatusException {

        return this.executeRUD(reqParamData);
    }

    /**
     * 数据执行
     */
    @PostMapping(value = "/execute")
    public Json execute(@RequestBody ReqParamData reqParamData) throws ResponseStatusException {

        return this.executeRUD(reqParamData);
    }
}
